package DBLayer;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.sql.Connection;

import ModelLayer.Apartment;
import ModelLayer.Contract;

public class DBContract implements IFDBContract {
	private Connection con;;
	private static DBContract dbCon = null;
	
	private DBContract() {
		con = DBConnection.getInstance().getDBcon();
	}
	
	public static DBContract getInstance(){
		if(dbCon==null) dbCon = new DBContract();
		return dbCon;
	}
	
	@Override
	public ArrayList<Contract> getAllContracts(){
		return miscWhere("");
	}
	
	@Override
	public ArrayList<Contract> getAllActiveContracts(){
		return miscWhere("DATEADD (mm,period,startDate) > GETDATE()");
	}
	
	@Override
	public ArrayList<Contract> getAllContracts_where(String wClause){
		return miscWhere(wClause);
	}
	
	@Override
	public ArrayList<Contract> searchRandom(String searchF) {
		return miscWhere("contractId LIKE '"+searchF+"' OR c.apartId LIKE '"+searchF+"' OR c.monthlyRent LIKE '"+searchF+"' OR depositAmount LIKE '"+searchF+"' OR startDate LIKE '"+searchF+"' OR period LIKE '"+searchF+"' OR tenantId LIKE '"+searchF+"' OR land.fname LIKE '"+searchF+"%' OR land.lname LIKE '"+searchF+"%' OR ten.fname LIKE '"+searchF+"%' OR ten.lname LIKE '"+searchF+"%' OR buildingName LIKE '"+searchF+"%' OR zone LIKE '"+searchF+"%' OR noOfRooms LIKE '"+searchF+"'");
	}
	
	@Override
	public Contract getContract_byApartId(int apartId) {
		return singleWhere("c.apartId="+apartId+" AND DATEADD (mm,period,startDate) > GETDATE()");
	}

	
	@Override
	public ArrayList<Contract> getContract_byApartmentId(int apartId){
		return miscWhere("c.apartId="+apartId);
	}
	
	@Override
	public Contract getContract_byContractId(int contractId){
		return singleWhere("");
	}
	
	@Override
	public int getContractsExpiringThisMonth(){
		String query = "SELECT COUNT(*) FROM Contract WHERE YEAR(dateadd(mm,period,startDate))=YEAR(getdate()) AND MONTH(dateadd(mm,period,startDate))=MONTH(getdate()) AND dateadd(mm,period,startDate)>getdate()";
		
		return countQuery(query);
	}
	
	
	@Override
	public int getContractsExpiringNextTwoMonths()
	{
		String query = "declare @start datetime, @end datetime \n"+
						"SET @start = dateadd(mm,1,getdate()) "+
						"SET @end   = dateadd(mm,1,@start) "+
						"SELECT COUNT(*), expire "+
						"FROM  (SELECT 0 as n,dateadd(mm,period,startDate) as expire FROM Contract ) x "+
						"WHERE (YEAR(expire)=YEAR(@start) AND MONTH(expire)=MONTH(@start)) OR (YEAR(expire)=YEAR(@end) AND MONTH(expire)=MONTH(@end))"+
						"GROUP BY x.expire";
		
		return countQuery(query);
	}
	
	
	private int countQuery(String query)
	{
		int count=0;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			ResultSet rs = stmt.executeQuery(query);
			if(rs.next()) count = rs.getInt(1);
		}
		catch(Exception ex){
			ex.printStackTrace();
		}
		
		return count;
	}
	
	@Override
	public Contract getContract_byTenantId(int tenantId){
		return singleWhere("c.tenantId="+tenantId+" AND DATEADD (mm,period,startDate) > GETDATE()");
	}
	
	@Override
	public Contract getContract_byTenantName(String tenantName){
		return singleWhere("(ten.fname LIKE '"+tenantName+"%' OR ten.lname LIKE '"+tenantName+"%') AND DATEADD (mm,period,startDate) > GETDATE()");
	}
	
	public ArrayList<Contract> getContract_byTenant_or_ByLandlord_Name(String name){
		return miscWhere("");
	}

	public int insert(Contract contracts){
		int rc = -1;
		String query = "INSERT INTO Contract (startDate,monthlyRent,depositAmount,period,apartId,tenantId,commission) VALUES ('"+contracts.getDate()+"', "+contracts.getApartment().getMonthlyRent()+","+contracts.getApartment().getDeposit()+","+contracts.getPeriod()+","+contracts.getApartment().getApartId()+","+contracts.getTenant().getPid()+","+contracts.getCommission()+")";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert Contract: "+e.getMessage());
		}
		
		return rc;
	}
	
	
	
	public int update(Contract contracts, boolean updateCommissionSimilar)
	{
		int rc = -1;
		String query ="UPDATE Contract SET startDate='"+contracts.getDate()+"', monthlyRent ="+contracts.getApartment().getMonthlyRent()+",depositAmount ="+contracts.getApartment().getDeposit()+",period = "+contracts.getPeriod()+",commission = "+contracts.getCommission()+" WHERE contractId = "+contracts.getContractId()+"";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			if(updateCommissionSimilar)
			{
				String updateCommission = "UPDATE Contract SET commission="+contracts.getCommission()+" WHERE apartId IN (SELECT ap.apartId FROM Apartment ap WHERE (ap.autoLevy | ap.autoRate)=(SELECT (app.autoLevy | app.autoRate) FROM Apartment app WHERE app.apartId="+contracts.getApartment().getApartId()+"))";
				
				rc = stmt.executeUpdate(updateCommission);
			}
			
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not update the Contract "+e.getMessage());
		}
		
		return rc;
	}
	
	
	
	
	public int delete(int contractId){
		int rc = -1;
		String query = "DELETE FROM Contract WHERE contractId="+contractId;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not delete Contract: "+e.getMessage());
		}
		return rc;
	}
	
	private Contract singleWhere(String wClause){
		Contract contracts = new Contract();
		
		ResultSet result;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			if(result.next()){
				contracts = buildContracts(result);
			}
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not execute select Contract query "+e.getMessage());
		}
		return contracts;
	}
	
	private ArrayList<Contract> miscWhere(String wClause){
		ArrayList<Contract> contracts = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				contracts.add(buildContracts(results));
			}
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not execute multiple select Contract query "+e.getMessage());
		}
		
		return contracts;
	}
	
	private String buildQuery(String wClause){
		String query = "SELECT contractId,startDate,c.monthlyRent,depositAmount,period,c.apartId,tenantId,commission,CASE WHEN dateadd(mm,period,startDate) < getdate() THEN 'true' ELSE 'false' END AS expired "
				+ "FROM Contract c "
				+ "INNER JOIN Apartment ap ON c.apartId=ap.apartId "
				+ "INNER JOIN People ten ON tenantId=ten.pid "
				+ "INNER JOIN People land ON ap.landLordId=land.pid "
				+ "INNER JOIN Building b ON ap.buildingNr=b.buildingNr";
		
		if(wClause.length()>0)  query += " WHERE "+wClause;
		
		return query;
	}
	
	private Contract buildContracts(ResultSet result)
	{
		Contract contracts = new Contract();
		
		try{
			contracts.setContractId(result.getInt("contractId"));
			contracts.setDate(result.getDate("startDate"));
			contracts.setPeriod(result.getInt("period"));
			contracts.setCommission(result.getDouble("commission"));
			contracts.setExpired(result.getBoolean("expired"));
			
			Apartment apart = DBApartment.getInstance().find(result.getInt("apartId"), false);
			apart.setMonthlyRent(result.getDouble("monthlyRent"));
			apart.setDeposit(result.getDouble("depositAmount"));
			
			contracts.setApartment(apart);
			contracts.setTenant(DBTenant.getInstance().getTenantById(result.getInt("tenantId")));
			
		} catch (Exception e){
			System.err.println("Could not build the Contract result "+e.getMessage());
		}
		return contracts;
	}
}
